const fs = require('fs').promises;
const path = require('path');
const connection = require('../../sql');
const { v4: uuidv4 } = require('uuid');
const { FILE_UPLOAD_IP } = require('../../config');
const uploadDir = path.join(__dirname, '../../upload');

const fileTypeMap = {
  picture: ['.png', '.jpg', '.jpeg', '.gif', '.bmp'],
  excel: ['.xls', '.xlsx'],
  pdf: ['.pdf'],
  word: ['.doc', '.docx'],
  ppt: ['.ppt', '.pptx'],
  video: ['.mp4', '.avi', '.mov', '.wmv']
};

class FileService {
  // 上传文件
  getFileCategory(ext) {
    ext = ext.toLowerCase();
    for (const [category, extensions] of Object.entries(fileTypeMap)) {
      if (extensions.includes(ext)) return category;
    }
    return 'other';
  }
  async uploadFile(file, dirTag, createId) {
    const date = new Date();
    const yearMonthDay = `${date.getFullYear()}${String(date.getMonth() + 1).padStart(2, '0')}${String(date.getDate()).padStart(2, '0')}`;
    const ext = path.extname(file.originalFilename).toLowerCase();
    const category = this.getFileCategory(ext);
    const destDir = path.join(uploadDir, dirTag, yearMonthDay, category);
    await fs.mkdir(destDir, { recursive: true });
    const destPath = path.join(destDir, file.originalFilename);
    await fs.access(file.filepath); // 检查临时文件存在
    await fs.rename(file.filepath, destPath); // 移动文件
    const objectName = path.join(dirTag, yearMonthDay, category, file.originalFilename).replace(/\\/g, '/');
    const url = `${FILE_UPLOAD_IP}${objectName}`;
    const eTag = `${Date.now()}${Math.random().toString(16).slice(2)}`.slice(0, 32);
    const fileInfo = {
      filename: file.originalFilename,
      dir_tag: dirTag,
      size: file.size,
      url,
      create_time: date,
      object_name: objectName,
      context_type: file.mimetype,
      e_tag: eTag
    };
    const statement = `INSERT INTO sys_file (filename, dir_tag, size, url, create_time, object_name, context_type, e_tag, create_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const [result] = await connection.execute(statement, [
      fileInfo.filename,
      fileInfo.dir_tag,
      fileInfo.size,
      fileInfo.url,
      fileInfo.create_time,
      fileInfo.object_name,
      fileInfo.context_type,
      fileInfo.e_tag,
      createId
    ]);

    return {
      url: fileInfo.url,
      filename: fileInfo.filename,
      objectName: fileInfo.object_name,
      dirTag: fileInfo.dir_tag,
      contextType: fileInfo.context_type,
      size: fileInfo.size,
      fileId: result.insertId,
      etag: fileInfo.e_tag
    };
  }

  // 删除文件
  async deleteFile(id, url) {
    let fileRecord;
    // 根据 id 或 url 查询文件记录
    if (id) {
      const [rows] = await connection.execute('SELECT * FROM sys_file WHERE id = ?', [id]);
      fileRecord = rows[0];
    } else if (url) {
      const [rows] = await connection.execute('SELECT * FROM sys_file WHERE url = ?', [url]);
      fileRecord = rows[0];
    } else {
      throw new Error('必须提供 id 或 url');
    }
    if (!fileRecord) {
      throw new Error('文件记录不存在');
    }
    // 构造文件路径
    const filePath = path.join(uploadDir, fileRecord.object_name);
    // 删除物理文件
    try {
      await fs.access(filePath); // 检查文件是否存在
      await fs.unlink(filePath);
    } catch (err) {
      if (err.code === 'ENOENT') {
        // console.warn('物理文件已不存在，继续删除数据库记录');
      } else {
        throw err; // 其他错误抛出
      }
    }
    // 删除数据库记录
    const [result] = await connection.execute('DELETE FROM sys_file WHERE id = ?', [fileRecord.id]);
    if (result.affectedRows === 0) {
      throw new Error('数据库记录删除失败');
    }
    return { id: fileRecord.id };
  }

  //查询文件
  async listFiles({ filename, current, limit }) {
    const offset = (current - 1) * limit;

    // 构建查询条件
    let whereClause = '';
    const countParams = [];
    const listParams = [];
    if (filename) {
      whereClause = 'WHERE filename LIKE ?';
      countParams.push(`%${filename}%`);
      listParams.push(`%${filename}%`);
    }

    // 查询总记录数
    const countStatement = `
		  SELECT COUNT(*) as total
		  FROM sys_file
		  ${whereClause}
		`;
    const [countResult] = await connection.execute(countStatement, countParams);
    const total = countResult[0].total;
    const totalPage = Math.ceil(total / limit);

    // 查询文件列表
    const listStatement = `
		  SELECT
			id,
			filename,
			dir_tag AS dirTag,
			size,
			url,
			create_time AS createTime,
			object_name AS objectName,
			context_type AS contextType,
			create_id AS createId,
			e_tag AS eTag
		  FROM sys_file
		  ${whereClause}
		  ORDER BY create_time DESC
		  LIMIT ? OFFSET ?
		`;
    listParams.push(String(limit), String(offset));
    const [rows] = await connection.execute(listStatement, listParams);

    return {
      current,
      limit: Number(limit),
      totalPage,
      total,
      rows
    };
  }
}

module.exports = new FileService();
